package com.tedu.bank_ems1.dao.impl;

import com.tedu.bank_ems1.dao.EmpDao;
import com.tedu.bank_ems1.entity.Emp;
import com.tedu.bank_ems1.entity.Emp;
import com.tedu.bank_ems1.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Author：毕龙
 * Date: 2024/6/25 9:56
 */
public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> listEmp() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Emp> list = null;
        try {
            //加载驱动
            //通过一个类的信息获取类对象 类加载
            Class.forName("com.mysql.cj.jdbc.Driver");

            //获取链接
            String url = "jdbc:mysql://localhost:3306/ems";
            String mysqlEmp = "root";
            String mysqlPass = "root";
            conn = DriverManager.getConnection(url, mysqlEmp, mysqlPass);

            //执行sql
            String sql = "select e.id, e.name, e.salary, e.birthday,\n" +
                    "       e.age, e.dept_id, d.name\n" +
                    "       from emp e\n" +
                    "left join dept d on d.id = e.dept_id";
            pstm = conn.prepareStatement(sql);

            //处理查询结果
            rs = pstm.executeQuery();
            list = new ArrayList<>();
            while (rs.next()) {
                Emp emp = new Emp();
                emp.setId(rs.getInt(1))
                        .setName(rs.getString(2))
                        .setSalary(rs.getDouble(3))
                        .setBirthday(rs.getDate(4))
                        .setAge(rs.getInt(5))
                        .setDeptId(rs.getInt(6))
                        .setDeptName(rs.getString(7));
                list.add(emp);
            }


            //释放资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public int insertEmp(Emp emp) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
             conn = JDBCUtils.getConnection();
            String sql = "insert into emp(name, salary, birthday, age, dept_id) values (?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,emp.getName());
            pstm.setDouble(2, emp.getSalary());
            //将utilDate转化为sqlDate
            Date date = new Date(emp.getBirthday().getTime());
            pstm.setDate(3,date);
            pstm.setInt(4,emp.getAge());
            pstm.setInt(5,emp.getDeptId());
            //执行sql
            pstm.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        }finally {
            JDBCUtils.release(null,pstm,conn);
        }

        return 1;
    }

    @Override
    public int deleteEmp(int id) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "delete from emp where id= ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            //执行sql
            pstm.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        }finally {
            JDBCUtils.release(null,pstm,conn);
        }

        return 1;
    }

    @Override
    public Emp getEmp(int id) {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstm = null;
        Emp emp = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id, name, salary, birthday, age, dept_id from emp where id= ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            //执行sql
           rs = pstm.executeQuery();
            if(rs.next()){
                emp = new Emp();
                emp.setId(rs.getInt(1))
                        .setName(rs.getString(2))
                        .setSalary(rs.getDouble(3))
                        .setBirthday(rs.getDate(4))
                        .setAge(rs.getInt(5))
                        .setDeptId(rs.getInt(6));
            }
        } catch (Exception e) {
            System.out.println(e);
        }finally {
            JDBCUtils.release(null,pstm,conn);
        }
        return emp;
    }

    @Override
    public int updateEmp(Emp emp) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "update emp set name=?,salary=?,birthday=?,age=? where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,emp.getName());
            pstm.setDouble(2,emp.getSalary());
            pstm.setDate(3,new Date(emp.getBirthday().getTime()));
            pstm.setInt(4,emp.getAge());
            pstm.setInt(5,emp.getId());
            //执行sql
            pstm.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        }finally {
            JDBCUtils.release(null,pstm,conn);
        }

        return 1;

    }
}
